EXCEL

PIVOT TABLE

CHAMPION

(Excel Champions Series)

HENRY E. MEJIA

EXCEL PIVOT TABLE CHAMPION

Copyright © 2018 HENRY E. MEJIA

All rights reserved. No part of this publication may be reproduced, stored in any data retrieval system or transmitted in any form or by any electronic, mechanical, photocopying, recording or other means, without the prior written permission of the author, except in the case of brief reviews used in literary reviews and certain non-commercial uses provided by the Copyright Law.

ABOUT THE AUTHOR

Henry E. Mejia is an online entrepreneur who discovered the great benefit of knowing how to use Microsoft Excel at an advanced level, and now he devotes part of his time to creating courses (books and videos) so that more people can enjoy free time and better opportunities that an Advanced Excel user can have.

Henry also realized that the vast majority of people give away a lot of their life in front of the computer. That time could be used in more productive or more enjoyable activities, only if people knew how to use Excel a little better.

The goal of Henry's books is to open the door for workers and business owners to use Excel more efficiently, so they can have more and better growth opportunities.

THANK YOU

Thank you for getting this book. This book is all about showing you to use Pivot Tables as fast as possible

If you want to help the author produce more books like this one please leave your honest review about this book on Amazon

CONTENTS

OTHER BOOKS BY THE AUTHOR

INTRODUCTION

GET YOUR 40 PRACTICE SPREADSHEETS (.xlsx)

CHAPTER 1: WHAT IS A PIVOT TABLE AND WHICH ARE ITS BENEFITS?

CHAPTER 2: SOURCE DATA

CHAPTER 3: PIVOT TABLE EDITOR/CREATOR PARTS

CHAPTER 4: PIVOT TABLES WITH 2 CONDITIONALS

CHAPTER 5: PIVOT TABLE VALUES OPTIONS

CHAPTER 6: PIVOT TABLES WITH 3 CONDITIONALS

CHAPTER 7: PIVOT TABLES WITH 4 CONDITIONALS

CHAPTER 8: DYNAMIC PIVOT TABLES WITH SLICERS”

CHAPTER 9: BONUS: EXCEL CONDITIONAL FORMATTING CHAMPION FRAGMENT

CHAPTER 10: BONUS: EXCEL VLOOKUP CHAMPION FRAGMENT

CHAPTER 11: QUICK FINAL TIPS

I WOULD LOVE TO READ YOUR COMMENTS

OTHER BOOKS BY THE AUTHOR

"THE ONLY EXCEL VLOOKUP FUNCTION BOOK YOU WILL EVER NEED" -  Sales Manager of an Oil Products Manufacturing Company

CLICK HERE TO KNOW MORE ABOUT “EXCEL VLOOKUP CHAMPION”

“THIS GREAT AND EASY TO UNDERSTAND BOOK TEACHES A VERY USEFUL WAY TO ANALIZE DATA" -  Accounting Manager of a Sportswear Company

CLICK HERE TO KNOW MORE ABOUT “EXCEL CONDITIONAL FORMATTING CHAMPION”

INTRODUCTION

Welcome to Excel Pivot Table Champion. This book was written to teach the correct way to use the Pivot Tables tool quickly and easily.

This book is part of the "Excel Champions" series and is written in a simple and clear language.

If you have ever been in a situation where you have a giant database (thousands of rows) you will agree that a tool that can make the calculations for you, and provide answers without errors in a few seconds would be a tool of incalculable value.

With the help of the Pivot Tables you will be able to obtain those answers in a fast and organized way, both for personal use information and for presentations at work meetings.

It's time to start your journey to become an Excel Pivot Table Champion.

GET YOUR 40 PRACTICE SPREADSHEETS (.xlsx)

Before starting Chapter 1 I recommend you get your 40 practice spreadsheets. Those exercise files are included for everyone who purchases this book. Those will serve you at the end of each chapter to reinforce what you have learned and make sure you have learned it well.

All you have to do is to send me an email to:

ems.online.empire@gmail.com

With the Subject “PIVOT TABLES PRACTICE SPREADSHEETS” and saying:

"Hello, I bought your book EXCEL PIVOT TABLE CHAMPION and I need the 40 practice spreadsheets"

I will gladly reply your email and send you the files.

Now you are ready to start Chapter 1. Let’s go!

CHAPTER 1

WHAT IS A PIVOT TABLE AND WHICH ARE ITS BENEFITS?

A Pivot Table is absolutely one of the best tools in Excel, the benefits of knowing how to use this tool are a lot since in most cases it is almost impossible to manually perform the work done by the Pivot Table tool.

If you want to do the work manually you would spend at least 10 times more time, it is better to invest that time learning to use the Pivot Tables.

"By using a Pivot Table the right way, you can get the answers you want in 10% of the time (or less)"

A Pivot Table is used to manage giant data tables (which we will call Source Data from now on), so you can organize the information and get the relevant answers you want.

A Source data can have thousands and thousands of rows of information and dozens of columns. To show an example, let's say that a table contains the following columns of information:

Order ID

Date

Region

Sales Agent

Item

Units sold

Cort Per Unit

Revenue per unit

Profit per unit

Total Cost

Total Revenue

Total Profit

Express Shipping

That would be 13 columns, if the table had 1,000 Order IDs, you would have a Source Data with 13,000 individual cells.

If you are asked the following questions, what would you answer?

• Which agent sold more refrigerators in the North zone?

• How much profits have TVs generated with express delivery and without express delivery?

• What percentage of revenue does the West zone have in relation to total revenue?

Probably you tend to use filters and some formulas to answer these questions, losing your most valuable asset, your time. But if you know how to use the Pivot Tables, you can answer that in less than a minute (Literally in less than a minute when you're already a Pivot Table Champion)

Let's answer the first question from the previous example. Which agent sold more refrigerators in the North zone?

First, this is how the Source Data would be.

Note: (The Source Data with which we are going to practice throughout this book has 500 rows of information and 13 columns, 6500 data in total)

This would be the answer with a Pivot Table (which took less than 1 minute). Cassandra, Christiane and Ezequiel sold more Refrigerators, tied with 4 each in the North zone.

It may sound ironic, but the Pivot Table is probably the most useful tool and one of the easiest to use once you learn a few tricks.

The benefits of learning to use Pivot Tables are the following:

• You save a huge amount of time. The larger the Source Data, the more time you save, since you do not have to do calculations manually.

• You can create organized Tables just like you want to show the information, so in your work meetings you can perfectly communicate your insights

• You can combine it with the Conditional Formatting tool (see my Excel Conditional Formatting Champion book for more information)

• You can use charts that reflect the information of your Pivot Table

• You have access to answers and insights that you could not get in any other way.

QUICK CHAPTER SUMMARY:

• The Pivot Table is probably the tool that can save you more time and effort

• Answer almost all the questions you can ask about a Database (Source Data)

• You can get very good findings using Pivot Tables with your company information

• They are very simple to use once you have learned and practiced

CHAPTER 2

SOURCE DATA

WHAT IS EXACTLY A SOURCE DATA?

A database of several rows and columns, organized in a consistent way is the first thing we need to be able to use Pivot Tables. This database is known as Source Data because it is the source from which we obtain the information to create the Pivot Table.

It is very important that the Source Data has correct and reliable information, because with the Pivot Table you will organize that information to get the answers. If the Source Data has incorrect information, the answers you get will also be incorrect.

TRYING TO ANSWER A QUESTION WITH A SOURCE DATA AND WITHOUT PIVOT TABLE

Open your Chapter2SourceData.xlsx file and follow me in this example.

We have a Source Data of 500 Rows and we want to answer the following question WITHOUT USING PIVOT TABLES:

How much profits each product generated with express delivery and without express delivery?

The procedure would be:

1.- Apply filter to the Database

2.- Filter one item and filter by express delivery YES

3.- Sum and write the total

4.- Repeat the process filtering by express delivery NO

5.- Sum and write the total in some other cell

6.- Repeat steps 2 to 5 for each remaining product

7.- Organize the information in a new table showing the figures you wrote down

I invite you to go trough that process to get the 10 answers and to measure the time you use until you put the information in an orderly manner and ready to be communicated. Why 10 answers? Because there are 5 items and 2 types of shipping, a total of 10 results that we need.

TRYING TO ANSWER A QUESTION WITH A SOURCE DATA AND WITH PIVOT TABLE

Now I will show you the simple process of getting the 10 answers with a Pivot Table.

Important note: At this moment I will only guide you to build the Pivot Table and see how much time you can save without knowing how it works. In the following chapters I will explain in detail each step with questions and answers.

Step 1: Select the whole SOURCE DATA without filtering. From the names of each column to the last row (row 503 in the example)

In this image I used "Immobilize Panels" to show you that I did select from row 3 to 503

Step 2: Click on the tab called "Insert" and Click on "Pivot Table"

Step 3: A box like the following will appear. Leave everything as it is and click OK, you will notice that a new Sheet will open.

Step 4: You will notice on the left the area where your Pivot Table will be and on the right the rectangle that is called Pivot Table Fields, which is used to build your Pivot Table. In the upper part should appear the 13 Categories that are the 13 columns of the Source Data. In the lower part there is a quadrant composed of 4 parts (Filters, Rows, Columns and Values)

NOTE from Step 4: If you ever close the Pivot Table Fields by accident you have 2 options.

Option 4.1.- Right click anywhere in the area where your Pivot Table is (or would be) and select "show Field List"

Option 4.2.- Left click on your Pivot Table and click on the "Pivot Table Tools"> "Analyze"> "Field List" section. With that the Pivot Table Fields will appear again. Sometimes “Analyze” will be under the name “Options”, depending on your Excel version.

Step 5: The only thing you have to do to create your Pivot Table is to DRAG from above in the Pivot Table Field the Item, Express Shipping and Total Profit fields to the following boxes:

Items to Rows

Express Shipping to Columns

Total Profit to Values

This way you will immediately obtain this Pivot Table with the 10 answers to the question asked, automatically organized and without having to make any more effort.

Note about converting the numbers to Currency ($): If you want to convert them to Money ($), just select the whole area where the numbers of your Pivot Table are located and go to the upper section of the Ribbon> Start> Number> Select the Currency or Accounting option. Or select the numbers and click on the $ Symbol.

Note about the Pivot Table Color and Style: To change the style and color of the Pivot Table just click on the Pivot Table and a tab called Design will be displayed where you will find several styles.

You can easily see on the Pivot Table that the Home Theater provided the highest profits with express delivery, and the Kitchen sold better without express delivery. TVs and Refrigerators are the ones that are least profitable. All this information is useful to make decisions, and is within the reach of a few clicks with the Pivot Tables.

CONGRATULATIONS! YOU HAVE CREATED YOUR FIRST PIVOT TABLE!

As I mentioned before in the Important Note, in this chapter the objective is that you know the difference of time and  effort used between manual calculations and using Pivot Tables, however, at this moment you have created your first Pivot Table (perhaps not knowing very well how you did it). Maybe you do not know why each Category needs to be in each Section, and you have a lot of questions, we will answer them in the following Chapters. Now that you have noticed the difference, we can continue your training to become a Pivot Table Champion and know exactly the creation process.

QUICK CHAPTER SUMMARY:

The process of getting answers and organized information is much easier and faster with the Pivot Tables than without them (you have verified it yourself)

CHAPTER 3

PIVOT TABLE EDITOR/CREATOR PARTS

The "Pivot Table Fields" tool is the box that we saw in the previous chapter, which contains the list of categories and its bottom part is divided into 4 fields (Filters, Rows, Columns and Values).

That "Pivot Table Fields" tool we can also call it (within this book) Pivot Table Creator because it serves to create it, or Pivot Table Editor because it also serves to edit it.

The way you want to call it, the most important thing is to know that this is the most important part of the Pivot Table, since any change you want to make to your Pivot Table must be made in the Pivot Table Fields / Creator / Editor.

The only changes that are made without the Pivot Table Fields are the aesthetics and changes to format the table.

PARTS OF THE PIVOT TABLE EDITOR

It is important to know the function of each part of the Pivot Table Editor, that way it will be easier for you to build your own Pivot Tables.

LIST OF CATEGORIES

As you can see the previous chapter, above you have the list of categories. This list appears automatically and is taken from the names of the columns of the Source Data. If a category is called "ITEM" in the Source Data, but you want it to appear as "SMALL ITEMS" in the Pivot Table, you need to make the change in the Source Data and then update the Pivot Table.

The list of categories is there for 2 reasons:

1.- So you can drag the Categories to some part of the quadrant that is below, and thus be able to build your Pivot Table.

2.-To indicate which Categories you have already added in your Pivot Table.

ROWS

It is the field that you will use for the category that you want to appear as a List.

STOP!!

I’m going to ask you from this moment to open the file Chapter3Sections.xlsx so that, at the same time that I explain WHY it happens what happens with the Pivot Tables in each section, you can also start practicing and earning experience.

Also, I’m going to ask you to follow the steps in the previous chapter (from step 1 to step 4) so we can practice with the Pivot Table Fields / Editor / Creator

Assuming that you have already selected your Source Data, you have already clicked on the Pivot Tables option and that you already have in front of you your Pivot Table Fields, let's continue with the explanation of the Rows.

Normally in the Rows you need to put the Category that we want to classify or divide. In addition to that almost always, when talking about people, the name of the people goes into the Rows.

In the question, How much is the total Revenue per Sales Agent? The Sales Agent category is the one that we would drag to the Rows section, and the Total Revenue in the Values section. (Try it in your Excel file, and you'll see that you'll get something like this)

In another example, if you wanted to know the revenue by Region, you would remove the Category "Sales Agents" from the Rows section (dragging it back to the list of categories), and you would place Region in the Rows section, and Total Revenue in the Values section. (Try it and you'll get this)

With these examples you can have an idea of the function that the Rows section has. You can continue playing with the other categories in the Rows section so you can see what happens. When you want to start over, just drag all the categories back to the list of categories at the top of the Pivot Table Fields.

COLUMNS

Now let's combine more categories. If you need to find the total revenue by Sales Agent and bye Region, it becomes necessary to use the Columns. In your Pivot Table, drag the Sales Agents into the Rows section, the Region into the Columns section and the Total Revenue into the Values section. You will see that you get a table similar to this one.

You can see that Sale Agents are arranged in rows and Regions in columns.

Another good example would be Revenue by Item and Region. Placing Item in Rows and Region in Columns. Try it on your Pivot Table.

VALUES

At this point you will know what the Values section is used for, but we will review it anyway.

Let's go back to the previous example, where we organize the Items in Rows, the Region in the Columns and the Total Revenue in the Values. If in that same example we eliminate the Total Revenue of the Value section and use Total Profits in that same section, what we will obtain is the sum of the Total Profits (not the Revenue) of the Items by Regions. And the Pivot Table would give you the following data. Try it in your file.

Basically, the Values section is the one that indicates which category will be calculated.

FILTERS

The Filters section will help you to have flexibility in your Pivot Table. For the example of this section of Filters, we will return to the previous Pivot Table with Items in Rows, Regions in Columns and Total Profits in Values.

If you add the Sales Agents to filters in Pivot Table, you will notice that apparently nothing changes, but a new tab appears in cell B1.

If you click on this new filter, you can filter the results by Sales Agent. The Profits contributed by each Sales Agent can be reviewed within that Pivot Table individually. If you chose Cassandra you would see the Profits that Cassandra contributed to the company. If you select Cassandra and Erik you will see the sum of both in the Pivot Table.

It's a very efficient way to add flexibility to a Pivot Table.

SUBORDINATION

Subordination is NOT a section but it is an important feature when creating a Pivot Table. It means that 2 or more Categories can be placed in the same Section.

Returning to the previous example where the Pivot Table Creator contains the following:

Rows: Item

Columns: Region

Values: Total Profit

Filter: Sales Agents

I invite you to move the Sales Agents from Filters to Rows (just above the ITEM Category but in the same Rows section), and you will get a Pivot Table like the following:

Note that the Sales Agents and Items are displayed in the form of Rows, however the Item Category was SUBORDINATED to the Sales Agent Category. You can try to revert the position of those categories to see that the Sales Agents can also be subordinated to the Items category, showing a change in the shape of the Pivot Table.

The Subordination in each section serves to give you more possibilities in terms of the presentation of the Pivot Table, also it is useful to obtain information that would be difficult to achieve without the subordination

QUICK CHAPTER SUMMARY:

5 Parts make up the Pivot Table Editor: Categories, Rows, Columns, Values and Filters.

The Categories are dragged to the quadrant that is at the bottom of the Pivot Table Editor.

The Subordination of Categories is more useful than you think.

Are you enjoying this book?

Do you think it's easy to understand?

Have the exercises helped you learn faster?

Without knowing your thoughts I won’t know if the book has helped you to become a better Pivot Tables user.

You can share your thoughts with me by writing a Review

CHAPTER 4

PIVOT TABLES WITH 2 CONDITIONALS

WHAT IS A CONDITIONAL?

When I say "Conditional" I mean a question that includes 2 "Variables". In fact, without realizing it, in the previous chapters you have already worked with 2, 3 and 4 "Conditionals", but in this chapter (and in the following ones) we are going to do more exercises and answer several questions that will arise regarding this issue of Variables or Conditionals.

The following questions are examples of questions with 2 Conditionals.

• How much has each Sales Agent sold?

• How much Profit has each Item generated?

• How much has been sold in each Region?

• How many Units have been sold out of Each Item?

In all these questions, information is requested from 2 Categories of the Source Data:

• Sales and Agents

• Profits and Item

• Sales and Region

• Sold Units and Items

We will use these 4 questions as exercises for this Chapter.

EXERCISES

Exercise: Open your Chapter4Ex1.xlsx file and follow the exercise with me.

Question: How much has each Sales Agent Sold?

Step 1: Select all the Source Data and perform Steps 1 through 4 of Chapter 2 to create an empty Pivot Table, so that you have access to the Pivot Table Fields / Creator.

Pivot Table Location Note: In Step 3 (from Chapter 2) you have the option to choose whether you want your Pivot Table in a new WorkSheet or in an existing WorkSheet. Most of the time I use a New WorkSheet, however you can choose to place your Pivot Table next to your Data Source or anywhere else. How do you do it? Choose "Existing WorkSheet", then click on the small "Location" box and select the cell where you want your Pivot Table to be located.

Step 2: It is time for you to work with your 2 Conditionals, Total Revenue and Sales Agents. Based on the previous chapters you should already know that Sales Agents go in the Rows section and the Total Sales in Values.

The result is the Pivot Table above. Remember that to convert to Currency you need to select the numbers of the Pivot Table and click on the sign of $ on the Ribbon.

Exercise: Open your file Chapter4Ex2.xlsx and follow the exercise with me.

Question: How much Profit has each Item generated?

Step 1: Repeat Step 1 to Step 4 of Chapter 2

Step 2: Once you have your Pivot Table Fields in sight, work with your 2 Conditionals, placing the Items in Rows and the Total Profit in Values. You will get a Pivot Table like this one.

Exercise: Open your Chapter4Ex3.xlsx file and follow the exercise with me.

Question: How much has been sold in each Region?

Step 1: Repeat Step 1 to Step 4 of Chapter 2

Step 2: Once you have your Pivot Table Field, work with your 2 Conditionals, placing the Regions in Rows and the Total Revenue in Values. You will get a Pivot Table like this one.

Exercise: Open your Chapter4Ex4.xlsx file and follow the exercise with me.

Question: How many Units have been sold out of Each Item?

Step 1: Repeat Step 1 to Step 4 of Chapter 2

Step 2: Once you have your Pivot Table Fields in view, work with your 2 Conditionals, placing the Items in Rows and the Units in Values. You will get a Pivot Table like this one.

NOTE: In this case, the amounts DO NOT have a dollar symbol because they are not money, they are Sold Units. You can confirm that number with the entire Units Column in your Source Data.

Congratulations! Now you know how to create the basic Pivot Tables with 2 Conditionals. In the next chapter we will see an extra function that Pivot Tables have, we will use the same example of 2 Conditionals so you can learn the difference.

MORE EXERCISES:

Now it's your turn to solve the exercises of the following files:

Chapter4Ex5

Chapter4Ex6

Chapter4Ex7

Chapter4Ex8

Chapter4Ex9

QUICK CHAPTER SUMMARY:

The Conditionals are basically Variables within the question you are trying to answer. Normally they are the Categories (Columns) of the Source Data.

You can place your Pivot Table in a new WorkSheet or in an existing WorkSheet.

You must be careful to identify when to put dollar sign to the Pivot Table and when not to do so. Since the Pivot Table will NOT automatically place them.

CHAPTER 5

PIVOT TABLE VALUES OPTIONS

You have probably noticed that in the previous chapters our Pivot Table always ADDS the values, but we also have other options that we will analyze in this chapter.

It is possible that our Pivot Table shows a Count of the values, an Average, the Maximum and the Minimum. All this adds even more flexibility when creating our Pivot Table.

As always, I will give you the explanation at the same time that we practice with the examples.

COUNT

Exercise: Open your file Chapter5Ex1.xlsx and follow the exercise with me.

Question: How many sales did each Sales Agent make? (I am talking about the number of sales, I do not mean amount of money.)

Step 1: Repeat Step 1 to Step 4 of Chapter 2

Step 2: Once you have your Pivot Table Fields in sight, work with your 2 Conditionals, placing the Sales Agents in the Rows section and the Total Revenue in the Values field. The Pivot Table will look like this, but it is INCORRECT, because that is the SUM of the sales, not the Count.

Step 3: You need to Right Click on any value of the Pivot Table to be able to change from SUM to COUNT. After that go to "Summarize Values By"> Count

When you have done that, the Pivot Table will change and display a Total of 500 Sales distributed among your Sales Agents. Why? Because each row of the Source Data is counted as an Individual Sale. This is how the option Count is used.

You can easily see that Erik Vos is the one who made the most sales.

AVERAGE

In the same way you can use the Pivot Table to get the Average. Using the same example above, change from Count to Average and now you will see the Average for each Sale of each Sales Agent. In this case it is again about Money.

You can see that the average of all of them is similar. In other words, Sales Agents make sales of almost the same size, and the big difference in the result is the number of sales they make. With that information a sales manager can create a strategy.             

MAX AND MIN

The maximums and minimums work in the same way. You can try them and you will get 2 Pivot Tables like the following ones.             

The Maximums are very similar, however the Minimums are very variable. Leida and Selena had the highest minimums.

MORE EXERCISES:

Now it's your turn to solve the exercises of the following files:

Chapter5Ex2

Chapter5Ex3

Chapter5Ex4

Chapter5Ex5

Chapter5Ex6

Chapter5Ex7

Chapter5Ex8

QUICK CHAPTER SUMMARY:

In addition to adding, the Pivot Tables can Count, Average, look for Maximum and Minimum values.

The option you choose will depend on the question you are trying to answer.

CHAPTER 6

PIVOT TABLES WITH 3 CONDITIONALS

As you have seen in the previous chapters, the Conditionals are the Variables in the questions you are trying to answer. These are usually the Source Data Categories.

In this chapter, we will practice with 3 Conditionals questions and create more complex Pivot Tables.

Using our Source Data, some examples of 3 Conditionals could be the following:

• What is the Total Revenue by Sales Agent and by Region?

• How many Items were Sold in each Region?

• How many Order IDs (Individual Sales) did each Sales Agent do for each Item?

In each of the previous questions, 3 Conditionals are included and it is necessary to include them in our Pivot Table, and to answer them we will perform the exercises.

Exercise: Open your Chapter6Ex1.xlsx file and follow the exercise with me.

Question: What is the Total Revenue by Sales Agent and by Region?

You already know how to create your Pivot Table, so that step will not explain it to you. In case of doubt you can review chapters 2 to 4.

What it is necessary to explain is how to organize the 3 Conditionals in the Pivot Table / Fields Creator. Then after you have your Pivot Table Fields in sight, do the following.

Step 1: Ask yourself which are the conditionals in the question?. The answer is: Total Revenue, Sales Agents and Region.

Step 2: Ask yourself how do I organize them in the Quadrant? There it will depend if you want to place the Sales Agents in the form of Rows or Columns, but the most normal layout would be the following:

• Sales Agents in Rows

• Region in Columns

• Total Revenue in Values

Step 3: Ask yourself: Does Total Revenue in the Values section add, count or average? The answer is that you must add.

Step 4: Ask yourself If the Total Revenue in Values is talking about units or money? The answer is that it is money, therefore a dollar symbol is added.

You have your Pivot Table of 3 Conditionals created!

Exercise: Open your Chapter6Ex2.xlsx file and follow the exercise with me.

Question: How many Items were Sold in each Region?

Step 1: Ask yourself which are the conditionals in the question?. The answer is: Items, Units, Region.

Step 2: Ask yourself how do I organize them in the Quadrant?

• Items in Rows

• Region in Columns

• Units in Values

Step 3: Ask yourself: Units in the Values section must add, count or average? The answer is that they must add.

Step 4: Ask yourself: Units in the Values section are units or money? The answer is that they are units, therefore no dollar symbol is added.

This should be your Pivot Table.

Exercise: Open your Chapter6Ex3.xlsx file and follow the exercise with me.

Question: How many Order IDs (Individual Sales) did each Sales Agent for each Item?

Step 1: Ask yourself which are the Conditionals in the question?. The answer is: Order ID, Sales Agent and Items

Step 2: Ask yourself how do I organize them in the Quadrant?

• Sales Agent in Rows

• Items in Columns

• Order ID in Values

Step 3: Ask yourself Does the Order ID in the Values section add, count or average? The answer is that Order ID must COUNT.

Step 4: Ask yourself: Are the Units in the Values section units or are they money? The answer is that they are units, therefore no dollar symbol is added.

Congratulations! Now you can create Pivot Tables with 3 Conditionals!

MORE EXERCISES:

Now it's your turn to solve the exercises of the following files:

Chapter6Ex4

Chapter6Ex5

Chapter6Ex6

Chapter6Ex7

Chapter6Ex8

Chapter6Ex9

QUICK CHAPTER SUMMARY:

Pivot Tables with 3 Conditionals are very useful because they give a lot of information

The secret to creating them correctly is to complete the 4 questions described in this chapter

CHAPTER 7

PIVOT TABLES WITH 4 CONDITIONALS

The Pivot Tables of 4 Conditionals are probably the most complex Pivot Table you will use at work. They are rarely necessary because you can use 2 Pivot Tables of 2 or 3 Conditionals to get the information, but if you want to be a Pivot Table Champion you should consider learning how to use them.

They are not difficult when you already know how the Pivot Tables of 2 and 3 Conditionals work. The only big difference is that it is mandatory that one Category be Subordinated to another, and it is usually subordinated in the Rows section.

Using our Source Data, some examples of 3 Conditionals could be the following:

• What is the Total Revenue of each Sales Agent by Item and by Region?

• How many Order IDs (individual Sales) did each Sales Agent do for each Item and in each Region?

To make your Pivot Table of 4 Conditionals correctly, it would be convenient to ask the same questions as in the previous chapter (the same reasoning process)

Exercise: Open your Chapter7Ex1.xlsx file and follow the exercise with me.

Question: What is the Total Revenue of each Sales Agent by Item and by Region?

Step 1: Ask yourself which are the conditionals in the question?. The answer is: Total Revenue, Sales Agent, Item and Region

Step 2: Ask yourself “how do I organize them in the Quadrant?”

• Total Revenue in Values. Why? Because it is the numerical information we are looking for

• Sales Agent in Rows. Why? Because it is the most natural and there are more Sales Agents than Items and Regions. If you put it in Columns it would be a Pivot Table very extended towards the sides.

• Option 1: Items in Columns and Region in Rows subordinated to Sales Agent (below)

• Option 2: Region in Columns and Items in Rows subordinated to Sales Agent (below)

Step 3: Ask yourself: Does Total Revenue in the Values section add, count or average? The answer is that it must add.

Step 4: Ask yourself “Is Total Revenue in the Securities section units or is it money?” The answer is that it is money, therefore a symbol of dollars is added.

You will get a very large Pivot Table, here I show you only one part. You can see that the Regions are Subordinated to the Sales Agents, therefore the Regions will be repeated under each name of the sellers. To the right of each Sales Agent (horizontally) is the Total Revenue by Item and in the last Column the Total Revenue by Region.

Exercise: Open your file Chapter7Ex2.xlsx and follow the exercise with me.

Question: How many Order IDs (individual Sales) did each Sales Agent for each Item and in each Region?

Step 1: Ask yourself “Which are the conditionals in the question?”. The answer is: Order ID, Sales Agent, Item and Region

Step 2: Ask yourself how do I organize them in the Quadrant?

• Order ID in Values. Why? Because it is the numerical information we are looking for

• Sales Agent in Rows. Why? Because it is the most natural and there are more Sales Agents than Items and Regions. If you put it in Columns it would be a Pivot Table very extended towards the sides.

• Option 1: Items in Columns and Region in Rows subordinated to Sales Agent (below)

• Option 2: Region in Columns and Items in Rows subordinated to Sales Agent (below)

Step 3: Ask yourself “Does the Order ID in the Values section add, count or average?· The answer is that it must Count.

Step 4: Ask yourself “Is the Order ID in the Values section units or is it money?” The answer is that it is NOT money, therefore no dollar symbol is added.

Very similar to the previous example (I also show only a part of the Pivot Table). You can see that in this example the Items are Subordinated to the Sales Agents and the Regions are in the Columns.

Some Pivot Tables have one unique way in which they can be created, some others can be created in different ways and it is up to you to choose the best one.

MORE EXERCISES:

Now it's your turn to solve the exercises of the following files:

Chapter7Ex3

Chapter7Ex4

Chapter7Ex5

Chapter7Ex6

QUICK CHAPTER SUMMARY:

Pivot Tables of 4 Conditionals are the most complex.

The creation process is exactly the same as the ones of 2 and 3 Conditionals.

The difference is that it is necessary that one Category is Subordinated to another (usually in the Rows section)

CHAPTER 8

DYNAMIC PIVOT TABLES WITH SLICERS

So far you have learned everything you need to build useful Pivot Tables in your work, but there is still something else I want to teach you.

In this chapter I will teach you how to build dynamic Pivot Tables with Slicers, so when you create your Pivot Table you can filter the information with a few clicks and get answers to different questions WITHOUT needing to create another PIVOT TABLE.

A Slicer contains buttons for all the data of a specific Category. For example, the Slicer in the Items category would have Refrigerator, TV, Home Theather, Kitchen and Air Conditioner.

When you click on each button the values of the Pivot Table are Filtered, adjusting the information only to the buttons that you leave active. For example, you may want to know the Revenue value of the TV and the Refrigerator, but completely omitting the Revenue from the other 3 Items (which do not even appear in the Pivot Table). That's what you can do with the Slicers.

You can also create more than 1 Slicer at a time, so it's as if you have a Dynamic Filter, with which you can visualize the information you want in a single Pivot Table.

To explain it better let's do an exercise together:

Exercise: Open your Chapter8Ex1.xlsx file and follow the exercise with me.

Question: How much Total Revenue did each Sales Agent do in each Region? ADDING SLICERS FOR ITEMS AND FOR EXPRESS SHIPPING

Step 1: The first thing you should do is to create the Pivot Table as you normally would, I invite you to do it and from step 2 I will teach you to place the Slicers and I will show you how they work.

Step 2: Once you have created your Pivor Table you must click anywhere in your Pivot Table, then go to the Section “Pivot Table” > Tools> Analyze> Insert Slicer

NOTE: "Analyze" in some versions of Excel is called "Options"

Step 3: You will find a window with all the Categories, select the Categories Item and Express Shipping and finally click on OK

Now you watch the Pivot Table along with the created Slicers.

How do the Slicers work?

If you only select the Item TV in the Items Slicer, you will notice that the Total Revenue of the entire Pivot Table changes.

Why? Because now you are observing the following:

1) The Total Revenue

2) Of each Sales Agent

3) By Region

4) ITEM "TV" exclusively (omitting all other items)

5) With and Without Express Shipping

Now try the following, keep the Item Slicer with TV button selected and in the Express Shipping Slicer select "YES". The Total Revenue changes again.

Why? Because now you are observing the following:

1) The Total Revenue

2) Of each Sales Agent

3) By Region

4) ITEM "TV" only (excluding all other items)

5) With Express Shipping (excluding shipments WITHOUT Express Shipping)

Now try the following, keep the Slicer the same with Express Shipping "YES", and in the Items Slicer select "TV", "Refrigerator" and "Kitchen" The Total Revenue changes again.

NOTE: To select more than 1 button you must keep the Ctrl key pressed.

Why? Because now you are observing the following:

1) The Total Revenue

2) Of each Sales Agent

3) By Region

4) ITEM "TV" plus "Refrigerator" plus "Kitchen" (excluding the other 2 Items)

5) With Express Shipping (excluding shipments WITHOUT Express Shipping)

This is how you can see the power of a Pivot Table with 3 Conditionals and 2 Slicers. Depending on the number of Categories, you can make Pivot Tables with the amount of Slicers that you think is convenient to filter the information. The process is exactly the same.

MORE EXERCISES:

Now it's your turn to solve the exercises of the following files:

Chapter8Ex2

Chapter8Ex3

Chapter8Ex4

Chapter8Ex5

Chapter8Ex6

QUICK CHAPTER SUMMARY:

Slicers are used as dynamic filters.

The information in Values changes each time you select the different Slicer buttons

You can combine many Slicers at the same time.

CONGRATULATIONS!

If you have completed the chapters and the exercises, you are now a Conditional Pivot Table Champion!

Because of your effort, I decided to share with you the first chapter of my book EXCEL VLOOKUP CHAMPION and EXCEL CONDITIONAL FORMATTING CHAMPION as a gift.

Also, in the last chapter I have included several tips that I consider important in your development as an Excel Champion.

CHAPTER 9

BONUS CHAPTER:

EXCEL CONDITIONAL FORMATTING CHAMPION FRAGMENT

WHAT IS CONDITIONAL FORMATTING AND WHICH ARE ITS BENEFITS?

As a Bonus and Free Gift, you are getting the 1st Chapter of my book “Excel Conditional Formatting Champion”.

If you want to, you can visit the full book website clicking here.

In Excel, "formatting" means visually changing a cell. Some examples of format are:

• Center the words

• To align to the right

• Fill the cell with one color

• Underline the words

• Words in bold

• Modify the font color

• Modify the size of the words

Conditionals in Excel are logical sequences that normally follow the pattern "If the CONDITION is met, then Excel FORMATS the cell". Some types of conditions are:

• The value of the cell is lower or higher than other

• The value of one cell is the same as another

• The cell contains a certain letter, word or phrase

• The values are duplicated within a group of several cells

• The values are within a range of dates

• The values are the minimum or maximum values within a group of cells

• Many other conditions can be added

So, to explain it simply, CONDITIONAL FORMAT means to automatically format a cell (with any of the previous formatting options) when the condition we want is fulfilled.

"CONDITIONAL FORMAT means to automatically format a cell when the condition we want is met"

With the conditional formatting tool, it is extremely easy to automatically highlight the information that interests you, allowing you to recognize important information for decision making in a matter of seconds.

CONDITIONAL FORMATTING EXAMPLES

EXAMPLE 1: Imagine for a moment that you need to find 10% of the higher values in the following table.

The conditional formatting tool helps you achieve it easily in less than 20 seconds. When you order Excel to find 10% of the higher values and color the cell in green automatically.

In this example, the condition was "10% of the top group values". The group consists of 65 values, 10% is 6.5, so Excel automatically formatted the 6 cells that are the higher.

EXAMPLE 2: Now we have a table with sales, names and brands of mobile phones. You want to find the 20 smallest values.

As I mentioned, with conditional format it is extremely easy to highlight information you need. In the image below, you can see that in a matter of seconds you found the 20 smallest values.

The relevant information that you can obtain in this simple example is that:

• Tonja is the one with the most difficulty to sell almost all the brands.

• The brand that sells more easily is Motorola.

That information is very valuable to make decisions. In this simple example you can realize that conditional format is really useful , now imagine the benefit you can get when you use that same tool for more complex situations.

EXAMPLE 3: Now you have a similar table with the profits per company and you want to know the best ones, the average ones and the worst ones. Also you want to know which year was the best, and you want the information in a simple and visual way.

Notice that in a matter of seconds you can color the values in a degraded way, the one with the darkest color is the highest value, the one with no color is the lowest value, and all the intermediate ones are shown with a color scale .

Additionally to the right is the sum of all of the sales of each year, which includes a bar visually indicating the information in the form of a graph. Those two things can also be done with conditional formatting.

The number of situations in which conditional formatting can be used are a lot and it usually offers a quick and visually comfortable solution to analyze and present information.

QUICK CHAPTER SUMMARY:

• The Conditional Formatting tool is used to format a cell automatically.

• Formatting means changing the shape, color, size or background of a cell or group of cells.

• Conditional format offers a quick and visually comfortable solution to analyze and present information.

CHAPTER 10

BONUS CHAPTER:

EXCEL VLOOKUP CHAMPION FRAGMENT

WHAT IS VLOOKUP AND WHICH ARE ITS BENEFITS?

As a Bonus and Free Gift, you are getting the 1st Chapter of my book “Excel Vlookup Champion”.

If you want to, you can visit the full book website clicking here.

WHAT IS A FUNCTION?

An Excel function is a tool used to make calculations, searches, changes or logical reasoning with data you provide, with the objective of returning a result.

Functions (or formulas) always start with the symbol of =

Every time you try to write a function you must select the cell and start with a =

In a simple way, using functions saves you the hard work and it is Excel who works hard to give you the result you are looking for. Less work for you and less time in the office!

"Using functions (formulas) saves you a lot of hard work."

Excel is one of the most powerful software, it has many functions and many tools, and in this book you will learn one of the most useful: VLOOKUP

WHAT IS VLOOKUP?

Vlookup is one of the most useful Excel functions. You can use it in different ways but basically it helps you find information within a giant amount of data.

IMPORTANT NOTE:

VLOOKUP is the formula in English.

If you use Excel in Spanish, the formula works the same, with the only difference that VLOOKUP in Spanish is called BUSCARV.

You only need to enter in the formula =BUSCARV instead of =VLOOKUP and voila, everything else remains the same.

Sometimes, you need to find the exact price (or any other information) of a certain Product ID, but you have many products. What someone who is not a Vlookup Champion does is to search the price of each product in their database and copy it manually, one by one. What a waste of time! What a Vlookup Champion does is to use the formula to get the right prices from the database and in 30 seconds all the prices along with their respective Product ID are in place, with zero errors.

If you have a list of names or numbers and you need to relate it to other data, Vlookup helps you find that information in a database, in just a few seconds and without errors

It is possible that you have a list of students, clients, stores, employees, vehicles, invoices, and you need to place certain information next to them without errors to be able to do an analysis with or without graphs, VLOOKUP helps you to do that in a matter of seconds.

EXAMPLE:

Imagine that you have this daily sales table, and you need to fill out the product description and the employee who sold it:

It would take you easily 3 or 4 minutes to fill it out manually and with the risk of having errors, and there are only 7 sales. When you face hundreds of sales you can end up with some complications. But if you have previously a "master table” where you have the list of all the product IDs and their descriptions (that "master table” is called Database and it is usually obtained from the ERP System of the business) you can use Vlookup and within 15 seconds you will have the product descriptions in order. Another 15 seconds and you would have the names of the employees too.

When you are a Vlookup Champion you need only 1 minute to search and write what used to take you 10 minutes without Vlookup

THE BENEFITS OF USING VLOOKUP AS A CHAMPION

Well, it's obvious that knowing how to use Vlookup doesn’t make you look sexier in the office (although sometimes it does) but it has many other benefits:

• You can save a lot of time of searching and filling spreadsheets with information of databases. That is, you have more chances to leave the office early and have done what they ask.

• Most of the time you will have zero errors, and when you have one you can immediately notice it. Never again give incorrect information to the boss. Avoid ending up the day with a long face.

• Vlookup makes it easy for you to learn other Excel functions. Combining functions is one way to get the best out of Excel, and using Vlookup as a Champion is a great start to achieve that.

• You can spend more time analyzing the information and searching ways to improve your work. How on earth can you analyze the information if you use hours to obtain it and organize it? By the time you finish you are tired, stressed and hungry! It’s better to use VLOOKUP.

Now that you understand the benefits of being a Vlookup Champion, you will be more motivated to be one. In the next chapter you will learn the parts that make up the Vlookup function quickly and easily. Once you understand the structure and its parts you will begin with the exercises.

QUICK CHAPTER SUMMARY

Vlookup will save you time and prevent you from making mistakes.

Vlookup will allow you to analyze more information and make better decisions in your job or business.

CHAPTER 11

QUICK FINAL EXCEL TIPS

This book wouldn’t be complete without a series of final recommendations that can help you to be not only a PIVOT TABLES Champion, but also a complete Excel Champion.

Here (in this short chapter) I can’t teach you everything I'm going to recommend because they are extensive topics that would not fit in a few pages, it is also information that I teach deeply in other Excel Champions books.

However, I want to make you the following recommendations with the hope that you recognize the main tools that you must learn to be an Excel Champion.             

WHY DO YOU NEED TO LEARN KEYBOARD SHORTCUTS?

First of all, I want to recommend that you learn Excel keyboard shortcuts. Keyboard shortcuts are the easiest and fastest way to increase your productivity in Excel. You can easily cut your work time in half.

The reality is that there are more than 100 keyboard shortcuts. My recommendation is that you learn the 10 or 20 main ones. Which are the main ones? The ones you use the most depending the kind of work you have to do in Excel.

Some of those that everybody should use are:

Ctrl + C to copy a cell (with format too)

Ctrl + V to paste the cell that you copied

Ctrl + X to cut the cell (instead of copying it, you remove it from its cell to paste it in another cell)

Ctrl + to insert a column or row (selecting the column or row previously)

Ctrl - to delete a column or row (selecting the column or row previously)

Surely with these shortcuts you can move a little faster. But there are more that are quite useful.

 WHY DO YOU NEED TO LEARN VLOOKUP?

The VLOOKUP formula is one of the most used FOR search and reference in Excel.

When you work with large amounts of data (numeric and text) it is very likely that you have to use the formulas VLOOKUP, IF, MATCH or INDEX.

Every one of these formulas are explained in my Excel Vlookup Champion book, which you can find in the following link: http://bit.ly/VLOOKUPCHAMPION

 WHY DO YOU NEED TO LEARN MORE FUNCTIONS?

There are hundreds of functions that can help you to better perform your work, however you may not know them. Sometimes a new function that you learn can save you hours of weekly work in the office.

The important fact to remember about functions is that they tend to relate to each other and become stronger tools when combined or in the form of nested formulas.

I'll give you an example: VLOOKUP. The VLOOKUP function is quite strong and useful on its own, but when you learn to use IF together with VLOOKUP, three things happen:

1) You learned a new function: VLOOKUP

2) You learned a new function: IF

3) You learned a new tool: IF + VLOOKUP

When you learn just two functions you actually have three tools in your toolbox. That is, your tools are not just the number of functions you master, but also include the combinations you can make between those functions.

So the more functions you know, the more combinations you can make and the more chances you have to become an Excel Champion.

WHY DO YOU NEED TO LEARN TO USE CHARTS?

Charts are, by excellence, the way to communicate quantitative information in the business world, in non-profit organizations, in schools, in governmental organizations, in health areas, in sports, etc.

It's very simple, if you want to effectively communicate your numerical data, you need to master the Excel Charts. That includes the use of tables and the correct positioning of them, the selection of the data that you need, the Chart Type selection and the modification of the parameters of the chart.

Additionally, it becomes necessary that you learn to discover what a chart wants to "tell you". Correctly analyzing the data in a chart usually leads to better decisions.

If you want to make better decisions in your job or company, it is very likely that becoming an Excel Charts and Graphs Champion will benefit you.

CONGRATULATIONS, YOU’VE MADE IT!

I WOULD LOVE TO READ YOUR COMMENTS

Before you go, I would like to tell you Thank You for buying my book. I hope that the information you obtained in Excel PIVOT TABLE Champion helps you in your job or business, and that you can have greater productivity and more free time to use it in the activities that you like the most.

I realize that you could have chosen among several other Excel books but you chose Excel PIVOT TABLE Champion and you invested your time and effort. I am honored to have the opportunity to help you.

I’d like to ask you a small favor. Could you take a minute or two and leave a Review of Excel PIVOT TABLE Champion on Amazon?

This feedback will be very appreciated and will help me continue to write more courses that help you and a lot more people.

Share your comments with me and other readers.